package dbtools;

/**
 * Title:SQL转换器,用于将基于DB2写法的SQL语句，转换成其它数据库写法的语句
 */

import java.util.ArrayList;
import java.util.StringTokenizer;

public class SqlTools {
	public static final String standquerysql(String sqlvalue, String dbname) {
		return tooraformat(sqlvalue);
	}

	public static final String standexecsql(String sqlvalue, String dbname) {
		return tooraexec(sqlvalue);
	}

	//转换为DB2 语法格式的SQL 语句
	private static String todb2format(String inputsql) {
		try {
			String newSql = toDb2To_char(inputsql); //to_char转换
			newSql = toDb2Interval(newSql); //interval转换
			newSql = toDb2To_date(newSql); //to_date转换
			newSql = toDb2Concat(newSql); //concat转换
			newSql = toDb2ToAll(newSql, "todate"); //todate转换
			newSql = toDb2ToAll(newSql, "todatetime"); //todatetime转换
			newSql = toDb2ToAll(newSql, "tochar"); //tochar转换
			newSql = toDb2ToAll(newSql, "tochartime"); //tochartime转换
			return newSql;
		} catch (Exception ex) {
			System.err.println(">>Error: 从Oralce SQL 转为 DB2 SQL 时，转换失败。");
			return inputsql;
		}
	}

	//转换为ORACLE 语法格式的SQL 语句
	private static String tooraformat(String inputsql) {
		try {
			String newSql = toOracleCreate(inputsql, "a.fd_create_time", 0);
			//fd_create_time处理
			newSql = toOracleCreate(newSql, "a.fd_create_date", 0);
			//fd_create_date处理
			newSql = toOracleTrunc(newSql);
			return newSql;
		} catch (Exception e) {
			System.err.println(">>Error: DB2 SQL 转为 ORACLE SQL 时，转换失败。");
			return inputsql;
		}
	}

	//转换为DB2 语法格式的执行语句
	private static String todb2exec(String inputsql) {
		return inputsql;
	}

	//转换为ORACLE 语法格式的执行语句
	private static String tooraexec(String inputsql) {
		return inputsql;
	}

	//oralce中to_char转换 成db2的类型，递归
	private static String toDb2To_char(String sql) {
		String sqlMiddle = sql.toLowerCase(); //转小写
		int beginTochar = sqlMiddle.indexOf("to_char");

		if (beginTochar > 0) {
			int beginA = sql.indexOf(")", beginTochar) + 1;
			String strOld = sql.substring(beginTochar, beginA); //需要更换的块区

			String beginSql = sql.substring(0, beginTochar);
			String endSql = sql.substring(beginA, sql.length());

			String strNew = (strOld.trim()).toLowerCase(); //trim掉 再转小写

			int beginB = strNew.indexOf("(") + 1;
			int beginC = strNew.indexOf(",", beginB);
			int beginD = strNew.indexOf(")", beginB);
			if (beginD < beginC || beginC < 0) {
				beginC = beginD;
			}
			String column = strNew.substring(beginB, beginC).trim(); //被操作的字段

			if (column.equals("sysdate")) {
				column = "current timestamp";
			}

			String strChange = "";
			if (strNew.indexOf("yyyy-mm-dd hh24:mi:ss") > 0) {
				strChange = "varchar(" + column + ")";
			} else if (strNew.indexOf("yyyy-mm-dd") > 0) {
				strChange = "date(" + column + ")";
			} else if (strNew.indexOf("yyyy") > 0) {
				strChange = "year(" + column + ")";
			} else if (strNew.indexOf("mm") > 0) {
				strChange = "month(" + column + ")";
			} else if (strNew.indexOf("dd") > 0) {
				strChange = "day(" + column + ")";
			} else if (strNew.indexOf("hh24:mi:ss") > 0) {
				strChange = "time(" + column + ")";
			} else {
				strChange = "varchar(" + column + ")";
			}

			String sqlNew = beginSql + strChange + endSql;
			return toDb2To_char(sqlNew);
		}
		return sql;
	}

	//oralce中interval转换 成db2的类型
	private static String toDb2Interval(String sql) {
		String sqlMiddle = sql.toLowerCase(); //转小写
		int beginInterval = sqlMiddle.indexOf("interval");

		if (beginInterval > 0) {
			StringTokenizer st = new StringTokenizer(sql, " ");
			ArrayList arr = new ArrayList();
			int i = 0;
			while (st.hasMoreTokens()) {
				String column = st.nextToken();
				if ((column.toLowerCase()).equals("interval")) {
					column = column.toLowerCase();
				}
				arr.add(i, column);
				i++;
			}

			ArrayList newArr = toDb2Interval(arr);
			StringBuffer bufferSql = new StringBuffer();

			for (int j = 0; j < newArr.size(); j++) {
				if (j > 0) {
					bufferSql.append(" ");
				}
				bufferSql.append((String) newArr.get(j));
			}
			return bufferSql.toString();
		}
		return sql;
	}

	//递归方法
	private static ArrayList toDb2Interval(ArrayList arr) {
		int num = arr.indexOf("interval");

		if (num > 0) {
			String columnOld = (String) arr.get(num - 2); //操作字段
			String column = "";
			if (columnOld.startsWith("(")) {
				if ("(sysdate".equals(columnOld.toLowerCase())) {
					column = "(current timestamp";
				} else {
					column = columnOld;
				}
			} else {
				if ("sysdate".equals(columnOld.toLowerCase())) {
					column = "current timestamp";
				} else {
					column = columnOld;
				}
			}

			String shijianOld = (String) arr.get(num + 1); //操作时间
			String shijian = "";
			if (shijianOld.startsWith("\'")) {
				shijian = shijianOld.substring(1, shijianOld.length() - 1);
			} else {
				shijian = shijianOld;
			}

			arr.set(num - 2, column);
			arr.remove(num);
			arr.set(num, shijian);
			return toDb2Interval(arr);
		}
		return arr;
	}

	//oralce中to_date转换 成db2的类型，递归
	private static String toDb2To_date(String sql) {
		String sqlMiddle = sql.toLowerCase(); //转小写
		int beginTodate = sqlMiddle.indexOf("to_date");

		if (beginTodate > 0) {
			int beginA = sql.indexOf(")", beginTodate) + 1;
			String strOld = sql.substring(beginTodate, beginA); //需要更换的块区

			String beginSql = sql.substring(0, beginTodate);
			String endSql = sql.substring(beginA, sql.length());

			String strNew = strOld.trim().toLowerCase(); //先trim，再转成小写

			int beginB = strNew.indexOf("(") + 1;
			int beginC = strNew.indexOf(",", beginB);
			int beginD = strNew.indexOf(")", beginB);
			if (beginD < beginC || beginC < 0) {
				beginC = beginD;
			}

			String column = strNew.substring(beginB, beginC).trim(); //被操作的字段

			if (column.equals("sysdate")) {
				column = "current timestamp";
			}

			String strChange = "date(" + column + ")";

			String sqlNew = beginSql + strChange + endSql;
			return toDb2To_date(sqlNew);
		}
		return sql;
	}

	//oralce中todate、todatetime、tochar、tochartime转换 成db2的类型，递归
	private static String toDb2ToAll(String sql, String toAll) {
		String sqlMiddle = sql.toLowerCase(); //转小写
		int beginTodate = sqlMiddle.indexOf(toAll);

		if (beginTodate > 0) {
			int beginA = sql.indexOf(")", beginTodate) + 1;
			String strOld = sql.substring(beginTodate, beginA); //需要更换的块区

			String beginSql = sql.substring(0, beginTodate);
			String endSql = sql.substring(beginA, sql.length());

			String strNew = strOld.trim().toLowerCase(); //先trim，再转换为小写

			int beginB = strNew.indexOf("(") + 1;
			int beginC = strNew.indexOf(",", beginB);
			int beginD = strNew.indexOf(")", beginB);
			if (beginD < beginC || beginC < 0) {
				beginC = beginD;
			}
			String column = strNew.substring(beginB, beginC).trim(); //被操作的字段

			if (column.equals("sysdate")) {
				column = "current timestamp";
			}

			if (toAll.equals("todate")) {
				column = "date(" + column + ")";
			} else if (toAll.equals("todatetime")) {
				column = "timestamp(" + column + ")";
			}
			String sqlNew = beginSql + column + endSql;
			return toDb2To_date(sqlNew);
		}
		return sql;
	}

	//oralce中concat转换 成db2的类型，递归
	private static String toDb2Concat(String sql) {
		String sqlMiddle = sql.toLowerCase(); //转小写
		int beginConcat = sqlMiddle.indexOf("concat");

		if (beginConcat > 0) {
			int beginA = sql.indexOf(")", beginConcat) + 1;
			String strOld = sql.substring(beginConcat, beginA);

			String beginSql = sql.substring(0, beginConcat);
			String endSql = sql.substring(beginA, sql.length());

			String strNew = strOld.trim();

			int beginB = strNew.indexOf("(") + 1;
			int beginC = strNew.indexOf(")", beginB);
			String columnOld = (strNew.substring(beginB, beginC)).trim();
			//需要更换的块区

			StringTokenizer st = new StringTokenizer(columnOld, ",");
			String column = "";
			int i = 0;
			while (st.hasMoreTokens()) {
				if (i == 0) {
					column += st.nextToken().trim();
				} else {
					column += "||" + st.nextToken().trim();
				}
				i++;
			}
			String sqlNew = beginSql + column + endSql;
			return toDb2Concat(sqlNew);
		}
		return sql;
	}

	//db2中fd_create_date字段的处理，递归
	private static String toOracleCreate(
		String sql,
		String column,
		int begin) {
		String sqlMiddle = sql.toLowerCase(); //转小写
		int beginCreateDate = 0;
		if (begin <= 0) {
			beginCreateDate = sqlMiddle.indexOf(column);
		} else {
			beginCreateDate = sqlMiddle.indexOf(column, begin);
		}

		if (beginCreateDate > 0) {
			char zifu = sql.charAt(beginCreateDate - 1);
			String beginSql = sql.substring(0, beginCreateDate);
			String endSql = sql.substring(beginCreateDate + 16, sql.length());
			String sqlEnd = "";

			if (zifu == ' ') {
				String tempSql = (beginSql.trim()).toLowerCase();
				if (tempSql.endsWith("(")) {
					sqlEnd = sql;
				} else {
					String sqlNew = "";
					if (column.equals("a.fd_create_date")) {
						sqlNew = beginSql + "tochar(" + column + ")" + endSql;
					} else if (column.equals("a.fd_create_time")) {
						sqlNew =
							beginSql + "tochartime(" + column + ")" + endSql;
					} else {
						sqlNew = beginSql + "tochar(" + column + ")" + endSql;
					}
					sqlEnd = sqlNew;
				}
			} else if (zifu == '(') {
				sqlEnd = sql;
			} else if (zifu == ',') {
				String sqlNew = "";
				if (column.equals("a.fd_create_date")) {
					sqlNew = beginSql + "tochar(" + column + ")" + endSql;
				} else if (column.equals("a.fd_create_time")) {
					sqlNew = beginSql + "tochartime(" + column + ")" + endSql;
				} else {
					sqlNew = beginSql + "tochar(" + column + ")" + endSql;
				}
				sqlEnd = sqlNew;
			} else {
				sqlEnd = sql;
			}
			return toOracleCreate(sqlEnd, column, beginCreateDate + 16);
		}
		return sql;
	}

	// oracle中转化decimal为trunc
	private static String toOracleTrunc(String sql) {
		String sqlMiddle = sql.toLowerCase(); //转小写
		int beginTrunc = sqlMiddle.indexOf("decimal");

		if (beginTrunc > 0) {
			int oneTrunc = sql.indexOf(",", beginTrunc);
			int twoTrunc = sql.indexOf(",", oneTrunc + 1);

			String beginSql = sql.substring(0, beginTrunc);
			String secondSql = sql.substring(beginTrunc + 7, oneTrunc);
			String thirdSql = sql.substring(twoTrunc, sql.length());
			String newSql = beginSql + "TRUNC" + secondSql + thirdSql;

			return toOracleTrunc(newSql);
		}
		return sql;
	}
}